---
title: SAIF.Business.View.Policy database tables
summary: Oracle tables and SQL Server BDS stored-procedure access used by this read-only library.
status: draft
owner: SAIF Corporation
last_reviewed: 2026-08-21
library: SAIF.Business.View.Policy
reference_type: embedded
---

# SAIF.Business.View.Policy database tables

This page catalogs the dual-database structure used by this library. Primary policy and association reads target Oracle across 17 unqualified tables, while secondary SQL Server BDS access is security-only and occurs exclusively through opaque `cp_*` stored procedures with no base tables addressable in source. All detected database operations are read-only, consistent with [BR-VP-027: Create/Insert/Update/Delete SQL are invalid](business-rules.md#br-vp-027-createinsertupdatedelete-sql-are-invalid).

## Table catalog

| Table | Database | Schema/Link | CRUD | Purpose | Primary business object |
| --- | --- | --- | --- | --- | --- |
| [AFFILIATE](#affiliate) | Oracle | `[unqualified]**` | R | Resolve officer-of-notice affiliate names for a policy address | `clsPolicyAddress` |
| [ASSOCIATION_TYPE_CODE](#association_type_code) | Oracle | `[unqualified]**` | R | Catalog association type metadata, source procedures, and security group names | `clsAssociationTypeCode`, `clsAssociationTypeCodeCollection` |
| [BUSINESS_AFFILIATE](#business_affiliate) | Oracle | `[unqualified]**` | R | Link policies to active affiliates for officer-of-notice lookup | `clsPolicyAddress` |
| [CANCELLATION](#cancellation) | Oracle | `[unqualified]**` | R | Retrieve latest unreinstated future cancellation date | `clsPolicyData` |
| [COMBO_POLICY_PERIOD](#combo_policy_period) | Oracle | `[unqualified]**` | R | Resolve discount-combination policy memberships by combo period | `PolicyAssociationList` |
| [ENDORSEMENT](#endorsement) | Oracle | `[unqualified]**` | R | Identify issued periods with printed endorsements | `clsAssociationTypeCode` |
| [ER_COMBINATION_POLICY](#er_combination_policy) | Oracle | `[unqualified]**` | R | Resolve experience-rating combination memberships | `PolicyAssociationList` |
| [NON_EMPR_ACCT](#non_empr_acct) | Oracle | `[unqualified]**` | R | Retrieve non-employer account identity and address details | `clsNonEmployerAccount` |
| [PA_AUDIT](#pa_audit) | Oracle | `[unqualified]**` | R | Evaluate audit access windows and retrieve latest final audit date | `clsPolicyData`, `clsPolicySecurity` |
| [PA_AUDITOR](#pa_auditor) | Oracle | `[unqualified]**` | R | Match auditor vendor numbers to audits | `clsPolicySecurity` |
| [PA_POLICY_PERIOD](#pa_policy_period) | Oracle | `[unqualified]**` | R | Map policies to audit sequences for auditor authorization | `clsPolicySecurity` |
| [POLICY](#policy) | Oracle | `[unqualified]**` | R | Resolve policy relationship rows, names, and rate-account parentage | `PolicyAssociationList`, `clsPolicySecurity` |
| [POLICY_PERIOD](#policy_period) | Oracle | `[unqualified]**` | R | Resolve active combo periods and maximum effective dates | `PolicyAssociationList` |
| [POLICY_PERIOD_ALL](#policy_period_all) | Oracle | `[unqualified]**` | R | Resolve latest issued policy periods across all policy-period rows | `clsAssociationTypeCode` |
| [POLICY_PERIOD_NON_WCIS](#policy_period_non_wcis) | Oracle | `[unqualified]**` | R | Supply issued non-WCIS periods for period-date derivation | `clsAssociationTypeCode` |
| [POLICY_RELATIONSHIP](#policy_relationship) | Oracle | `[unqualified]**` | R | Resolve manual parent/member policy associations | `PolicyAssociationList` |
| [TRADING_AS](#trading_as) | Oracle | `[unqualified]**` | R | Retrieve trading-as names by policy or trading sequence | `clsTradingAs`, `clsTradingAsCollection` |

## affiliate

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Resolve the affiliate person's name for the officer-of-notice string returned with policy address data. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `AFFILIATE_SSN`, `AFFILIATE_FIRST_NAME`, `AFFILIATE_INITIAL`, `AFFILIATE_LAST_NAME` |
| **Sequence** | N/A |
| **Related tables** | `BUSINESS_AFFILIATE` |
| **Business objects** | `clsPolicyAddress` |

### Operations referencing this table
- [Retrieve addresses](operations-processes.md)

### Business rules referencing this table
- [BR-VP-010: Demo users see masked data](business-rules.md#br-vp-010-demo-users-see-masked-data)

### SQL referencing this table
- [SQL-009: Officer-of-notice lookup](sql/SQL-009-officer-of-notice-lookup.md)

---

## association_type_code

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supply association-type descriptions, category codes, source procedure names, sort order, and optional security group names for policy association features. |
| **Access pattern** | direct SQL and stored procedure indirection |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR`, `ASSOCIATION_TYPE_CATEGORY_CODE`, `SOURCE_PROCEDURE_NAME`, `SECURITY_GROUP_NAME`, `SORT_ORDER`, `POLICY_HEADER_DEFAULT_FLAG` |
| **Sequence** | N/A |
| **Related tables** | `POLICY_PERIOD_ALL`, `ENDORSEMENT`, `POLICY_PERIOD_NON_WCIS` |
| **Business objects** | `clsAssociationTypeCode`, `clsAssociationTypeCodeCollection` |

### Business rules referencing this table
- [BR-VP-020: Association type is secured when security group set](business-rules.md#br-vp-020-association-type-is-secured-when-security-group-set)
- [BR-VP-021: Association category prefix classification](business-rules.md#br-vp-021-association-category-prefix-classification)

### SQL referencing this table
- [SQL-003: Period-date query](sql/SQL-003-period-date-query.md)
- [SQL-005: Association type code lookup](sql/SQL-005-association-type-code-lookup.md)

---

## business_affiliate

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Link the policy to an active officer-of-notice affiliate row. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `OFFICER_OF_NOTICE_FLAG`, `AFFILIATE_CANCEL_DATE`, `AFFILIATE_SSN` |
| **Sequence** | N/A |
| **Related tables** | `AFFILIATE` |
| **Business objects** | `clsPolicyAddress` |

### Operations referencing this table
- [Retrieve addresses](operations-processes.md)

### Business rules referencing this table
- [BR-VP-010: Demo users see masked data](business-rules.md#br-vp-010-demo-users-see-masked-data)

### SQL referencing this table
- [SQL-009: Officer-of-notice lookup](sql/SQL-009-officer-of-notice-lookup.md)

---

## cancellation

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Return the latest unreinstated, unrescinded cancellation date for a policy. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `CANCELLATION_SEQ_NUM`, `CANCEL_DATE`, `REINSTATE_DATE`, `RESCIND_DATE` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsPolicyData` |

### Operations referencing this table
- [Retrieve policy data](operations-processes.md)

### Business rules referencing this table
- [BR-VP-017: Future cancel date only for Pending Cancellation](business-rules.md#br-vp-017-future-cancel-date-only-for-pending-cancellation)

### SQL referencing this table
- [SQL-020: Future cancel date](sql/SQL-020-future-cancel-date.md)

---

## combo_policy_period

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Join a policy's active combination-period membership to sibling members in discount-combination association retrieval. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `ACTIVE_MEMBER_FLAG`, `POLICY_PERIOD_EFF_DATE`, `COMBO_PERIOD_SEQ_NUM`, `MIN_PREMIUM_MEMBER_FLAG` |
| **Sequence** | N/A |
| **Related tables** | `POLICY`, `POLICY_PERIOD` |
| **Business objects** | `PolicyAssociationList` |

### Operations referencing this table
- [Retrieve policy associations](operations-processes.md)

### Business rules referencing this table
- [BR-VP-024: Default association dates](business-rules.md#br-vp-024-default-association-dates)

### SQL referencing this table
- [SQL-034: Policy association list](sql/SQL-034-policy-association-list.md)

---

## endorsement

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Identify issued policy periods by printed P401 endorsement when deriving association header dates. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_PERIOD_EFF_DATE`, `POLICY_NUM`, `ENDORSE_CODE`, `PRINTED_DATE` |
| **Sequence** | N/A |
| **Related tables** | `POLICY_PERIOD_ALL`, `POLICY_PERIOD_NON_WCIS` |
| **Business objects** | `clsAssociationTypeCode` |

### SQL referencing this table
- [SQL-003: Period-date query](sql/SQL-003-period-date-query.md)

---

## er_combination_policy

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Resolve experience-rating combination members effective on the requested association date. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `ER_COMBINATION_SEQ_NUM`, `EFF_DATE`, `EXP_DATE` |
| **Sequence** | N/A |
| **Related tables** | `POLICY` |
| **Business objects** | `PolicyAssociationList` |

### Operations referencing this table
- [Retrieve policy associations](operations-processes.md)

### Business rules referencing this table
- [BR-VP-024: Default association dates](business-rules.md#br-vp-024-default-association-dates)

### SQL referencing this table
- [SQL-034: Policy association list](sql/SQL-034-policy-association-list.md)

---

## non_empr_acct

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Retrieve non-employer account master and mailing fields by account number. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `ACCT_NUM`, `ACCT_NAM`, `ADDR_LINE1`, `ADDR_LINE2`, `CITY_NAM`, `STATE_CD`, `ZIP_CD`, `ZIP_CD_EXT`, `PHONE_NUM` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsNonEmployerAccount` |

### SQL referencing this table
- [SQL-006: Non-employer account lookup](sql/SQL-006-non-employer-account-lookup.md)

---

## pa_audit

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supply audit completion/cancellation dates and audit identity for final-audit display and auditor authorization checks. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `PA_TYPE_CODE`, `PA_STATUS_CODE`, `END_DATE`, `PA_SLCTN_CATEGORY_GROUP_CODE`, `PA_AUDIT_SEQ_NUM`, `PA_REVISES_SEQ_NUM`, `PA_AUDITOR_ID`, `CANCEL_DATE`, `COMPLETED_DATE` |
| **Sequence** | N/A |
| **Related tables** | `PA_AUDITOR`, `PA_POLICY_PERIOD`, `POLICY` |
| **Business objects** | `clsPolicyData`, `clsPolicySecurity` |

### Operations referencing this table
- [Retrieve policy data](operations-processes.md)
- [Authorize a user for a policy](operations-processes.md)
- [Retrieve the accessible policy list](operations-processes.md)

### Business rules referencing this table
- [BR-VP-006: Auditor 60-day access window](business-rules.md#br-vp-006-auditor-60-day-access-window)

### SQL referencing this table
- [SQL-019: Last completed final-audit date](sql/SQL-019-last-audit-date.md)
- [SQL-024: Auditor access count](sql/SQL-024-auditor-access-count.md)
- [SQL-028: Fee-auditor policy list](sql/SQL-028-fee-auditor-policy-list.md)

---

## pa_auditOR

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Match an auditor vendor number to authorized audit assignments. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `PA_AUDITOR_ID`, `VENDOR_NUM` |
| **Sequence** | N/A |
| **Related tables** | `PA_AUDIT` |
| **Business objects** | `clsPolicySecurity` |

### Operations referencing this table
- [Authorize a user for a policy](operations-processes.md)
- [Retrieve the accessible policy list](operations-processes.md)

### Business rules referencing this table
- [BR-VP-006: Auditor 60-day access window](business-rules.md#br-vp-006-auditor-60-day-access-window)

### SQL referencing this table
- [SQL-024: Auditor access count](sql/SQL-024-auditor-access-count.md)
- [SQL-028: Fee-auditor policy list](sql/SQL-028-fee-auditor-policy-list.md)

---

## pa_policy_period

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Resolve the audit sequence numbers associated with the requested policy during auditor access checks. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `PA_AUDIT_SEQ_NUM`, `POLICY_NUM` |
| **Sequence** | N/A |
| **Related tables** | `PA_AUDIT` |
| **Business objects** | `clsPolicySecurity` |

### Operations referencing this table
- [Authorize a user for a policy](operations-processes.md)

### Business rules referencing this table
- [BR-VP-006: Auditor 60-day access window](business-rules.md#br-vp-006-auditor-60-day-access-window)

### SQL referencing this table
- [SQL-024: Auditor access count](sql/SQL-024-auditor-access-count.md)

---

## policy

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supply policy relationship parentage, combination account linkage, and policy names for association and auditor-list queries. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `POLICY_RELATIONSHIP_CODE`, `POLICY_RELATIONSHIP_POLICY_NUM`, `RATE_ACCOUNT`, `POLICY_NAME` |
| **Sequence** | N/A |
| **Related tables** | `POLICY_RELATIONSHIP`, `COMBO_POLICY_PERIOD`, `ER_COMBINATION_POLICY`, `PA_AUDIT` |
| **Business objects** | `PolicyAssociationList`, `clsPolicySecurity` |

### Operations referencing this table
- [Retrieve policy associations](operations-processes.md)
- [Retrieve the accessible policy list](operations-processes.md)

### Business rules referencing this table
- [BR-VP-014: Source policy excluded from associations by default](business-rules.md#br-vp-014-source-policy-excluded-from-associations-by-default)

### SQL referencing this table
- [SQL-028: Fee-auditor policy list](sql/SQL-028-fee-auditor-policy-list.md)
- [SQL-034: Policy association list](sql/SQL-034-policy-association-list.md)

---

## policy_PERIOD

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supply active policy-period windows for discount combinations and the maximum policy effective date for default association-date logic. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `POLICY_PERIOD_EFF_DATE`, `POLICY_PERIOD_EXP_DATE` |
| **Sequence** | N/A |
| **Related tables** | `COMBO_POLICY_PERIOD` |
| **Business objects** | `PolicyAssociationList` |

### Operations referencing this table
- [Retrieve policy associations](operations-processes.md)

### Business rules referencing this table
- [BR-VP-024: Default association dates](business-rules.md#br-vp-024-default-association-dates)

### SQL referencing this table
- [SQL-034: Policy association list](sql/SQL-034-policy-association-list.md)
- [SQL-035: Max policy period effective date](sql/SQL-035-max-policy-effective-date.md)

---

## policy_PERIOD_ALL

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Provide all policy-period rows used to derive the latest issued period per policy for association headers. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `POLICY_PERIOD_EFF_DATE`, `CANCEL_DATE`, `POLICY_PERIOD_EXP_DATE` |
| **Sequence** | N/A |
| **Related tables** | `ENDORSEMENT`, `POLICY_PERIOD_NON_WCIS` |
| **Business objects** | `clsAssociationTypeCode` |

### SQL referencing this table
- [SQL-003: Period-date query](sql/SQL-003-period-date-query.md)

---

## policy_PERIOD_NON_WCIS

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Contribute issued non-WCIS policy periods when deriving association header date ranges. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_PERIOD_EFF_DATE`, `POLICY_NUM`, `POLICY_PERIOD_STATUS_CODE` |
| **Sequence** | N/A |
| **Related tables** | `POLICY_PERIOD_ALL`, `ENDORSEMENT` |
| **Business objects** | `clsAssociationTypeCode` |

### SQL referencing this table
- [SQL-003: Period-date query](sql/SQL-003-period-date-query.md)

---

## policy_RELATIONSHIP

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Resolve manual parent/member policy associations and affiliate-type codes. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `MBR_POLICY_NUM`, `PARENT_POLICY_NUM`, `AFFIL_TYP_CD` |
| **Sequence** | N/A |
| **Related tables** | `POLICY` |
| **Business objects** | `PolicyAssociationList` |

### Operations referencing this table
- [Retrieve policy associations](operations-processes.md)

### Business rules referencing this table
- [BR-VP-014: Source policy excluded from associations by default](business-rules.md#br-vp-014-source-policy-excluded-from-associations-by-default)

### SQL referencing this table
- [SQL-034: Policy association list](sql/SQL-034-policy-association-list.md)

---

## trading_as

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Retrieve trading-as rows for either a single trading sequence or all rows for a policy. |
| **Access pattern** | direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `TRADING_SEQ_NUM`, `POLICY_NUM`, `BUS_SEQ_NUM`, `TRADING_AS_NAME`, `TRADING_AS_TYPE_CODE` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsTradingAs`, `clsTradingAsCollection` |

### SQL referencing this table
- [SQL-032: Trading-as by sequence number](sql/SQL-032-trading-as-by-seq-num.md)
- [SQL-033: Trading-as by policy number](sql/SQL-033-trading-as-by-policy-num.md)

---

## SQL Server BDS

BDS access in this library is entirely opaque stored-procedure based. The source code constructs a SQL Server `clsSQLDataLayer` at runtime from `BDS_USER`, `bdsDatabase`, and `BDS_DATABASENAME`, then executes `cp_policy_in_group`, `cp_claimprofile_get_group_policy_list`, `cp_claimprofile_get_sc_book`, `cp_get_agent_online_policy_list`, and `cp_external_client_lookup`. No SQL Server base-table names are referenced anywhere in compiled source, so no BDS table detail sections can be documented from repository evidence alone.

---

## Table relationship diagram

The following diagram shows relationships between tables used by this library.

```mermaid
erDiagram
    BUSINESS_AFFILIATE }o--|| AFFILIATE : "affiliate_ssn"
    POLICY_RELATIONSHIP }o--|| POLICY : "parent_policy_num"
    COMBO_POLICY_PERIOD }o--|| POLICY_PERIOD : "policy_num, policy_period_eff_date"
    COMBO_POLICY_PERIOD }o--|| POLICY : "policy_num"
    ER_COMBINATION_POLICY }o--|| POLICY : "policy_num"
    PA_AUDIT }o--|| PA_AUDITOR : "pa_auditor_id"
    PA_POLICY_PERIOD }o--|| PA_AUDIT : "pa_audit_seq_num"
    PA_AUDIT }o--|| POLICY : "policy_num"
```

## Related documentation
- [Index](index.md)
- [Overview](overview.md)
- [Architecture](architecture.md)
- [Dependencies](dependencies.md)
- [Configuration](configuration.md)
- [Operations and processes](operations-processes.md)
- [Business rules](business-rules.md)
- [Security and authorization](security-authorization.md)
- [SQL catalog](sql/index.md)
- [Procedures](procedures/index.md)
- [API reference](api/index.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- `SAIF.Business.View.Policy/Associations/clsAssociationTypeCode.vb`
- `SAIF.Business.View.Policy/Associations/clsAssociationTypeCodeCollection.vb`
- `SAIF.Business.View.Policy/clsNonEmployerAccount.vb`
- `SAIF.Business.View.Policy/clsPolicyAddress.vb`
- `SAIF.Business.View.Policy/clsPolicyData.vb`
- `SAIF.Business.View.Policy/clsPolicySecurity.vb`
- `SAIF.Business.View.Policy/clsTradingAs.vb`
- `SAIF.Business.View.Policy/clsTradingAsCollection.vb`
- `SAIF.Business.View.Policy/PolicyAssociations.vb`